Imports System.Security.Cryptography
Imports System.Text
Imports System.Configuration
Imports nsDataProvider = KDESolutions.Providers.DataProvider

Public Class DBCategoryProvider
    Inherits CategoryProvider
    Private Const cTrueValue = "Y"
    Private Const cFalseValue = "N"
    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String
    Private _TablePrefix As String = String.Empty

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property

    Public Overrides ReadOnly Property Name() As String
        Get
            Return "KDESolutions.Providers.Categories.DBCategoryProvider"
        End Get
    End Property

    Public Overrides ReadOnly Property Description() As String
        Get
            Return "Provider for storing categories"
        End Get
    End Property

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As System.Collections.Specialized.NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If
        '***************************************************************************
        '***************************************************************************
        '***************************************************************************
        'Begin of product registration
        Dim productKey As String = String.Empty
        Try
            If (config("ProductKey") Is Nothing) Then
                Dim newEx As New Exception("You must supply a product key!")
                Throw newEx
            ElseIf (config("ActivationDate") Is Nothing) Then
                Dim newEx As New Exception("You must supply an order date.")
                Throw newEx
            End If

            Dim tempProductKey As String = String.Empty
            Dim tempProductKey1 As String = String.Empty
            Dim productName As String = Me.GetType.Assembly.GetName.ToString.Split(",")(0)
            Dim productVersion As String = Me.GetType.Assembly.GetName.Version.ToString
            Dim orderDate As String = CType(config("ActivationDate"), Date).ToShortDateString



            'Encrypt the password
            Dim md5Hasher As New MD5CryptoServiceProvider()

            Dim hashedBytes As Byte()
            Dim encoder As New UTF8Encoding()
            tempProductKey = productName & productVersion & orderDate

            hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(tempProductKey))
            For Each i As String In hashedBytes
                tempProductKey1 = tempProductKey1 & i & "-"
            Next
            tempProductKey1 = tempProductKey1.Substring(0, (tempProductKey1.Length - 1))

            productKey = tempProductKey1.Split("-")(0) & tempProductKey1.Split("-")(3) & "-" & _
            tempProductKey1.Split("-")(5) & tempProductKey1.Split("-")(2) & "-" & tempProductKey1.Split("-")(7) & tempProductKey1.Split("-")(9) & _
            "-" & tempProductKey1.Split("-")(1) & tempProductKey1.Split("-")(4)

        Catch
            Dim newEx As New Exception("Error validating your product key.")
            Throw newEx
        End Try

        If (config("ProductKey") = productKey) = False Then
            Throw New ArgumentException("Invalid Product Key!")
        End If
        'End of product registration
        '************************************************************************
        '***************************************************************************
        '***************************************************************************


        If ((name Is Nothing) OrElse (name.Length = 0)) Then
            name = "CategoryProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "KDESolutions Category provider")
        End If

        _TablePrefix = GetConfigValue(config("tablePrefix"), "Cat")

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("DataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("DataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If
    End Sub

    Public Overloads Overrides Sub AddCategory(ByVal NewCategory As DataTypes.CategoryItem, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "INSERT INTO " & _TablePrefix & "_Categories(CategoryGroupId,CategoryId," & _
        "CategoryShortDescription,CategoryLongDescription,SortOrder,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) " & _
        "VALUES(?,?,?,?,?,?,?,?,?,?,?)"
        DAP.AddParameter("@CategoryGroupId", NewCategory.CategoryGroupId, DbType.String)
        DAP.AddParameter("CategoryId", System.Guid.NewGuid.ToString, DbType.String)
        DAP.AddParameter("CategoryShortDescription", NewCategory.CategoryShortDescription, DbType.String)
        DAP.AddParameter("CategoryLongDescription", NewCategory.CategoryLongDescription, DbType.String)
        DAP.AddParameter("SortOrder", NewCategory.SortOrder, DbType.UInt64)
        DAP.AddParameter("@CreatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@CreatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
    End Sub

    Public Overloads Overrides Sub AddCategory(ByVal CategoryGroupId As String, ByVal CategoryShortDescription As String, ByVal CategoryLongDescription As String, ByVal SortOrder As Integer, ByVal ParentCategoryId As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "INSERT INTO " & _TablePrefix & "_Categories(CategoryGroupId,CategoryId," & _
        "CategoryShortDescription,CategoryLongDescription,SortOrder,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) " & _
        "VALUES(?,?,?,?,?,?,?,?,?,?,?)"
        DAP.AddParameter("@CategoryGroupId", CategoryGroupId, DbType.String)
        DAP.AddParameter("CategoryId", System.Guid.NewGuid.ToString, DbType.String)
        DAP.AddParameter("CategoryShortDescription", CategoryShortDescription, DbType.String)
        DAP.AddParameter("CategoryLongDescription", CategoryLongDescription, DbType.String)
        DAP.AddParameter("SortOrder", SortOrder, DbType.UInt64)
        DAP.AddParameter("@CreatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@CreatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
    End Sub

    Public Overloads Overrides Sub AddCategoryGroup(ByVal NewCategoryGroup As DataTypes.CategoryGroupItem, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "INSERT INTO " & _TablePrefix & "_CategoryGroups(CategoryGroupId," & _
        "CategoryGroupDescription,CreatedBy,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) " & _
        "VALUES(?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@CategoryGroupId", System.Guid.NewGuid.ToString, DbType.String)
        DAP.AddParameter("@CategoryGroupDescription", NewCategoryGroup.CategoryGroupDescription, DbType.String)
        DAP.AddParameter("@CreatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@CreatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
    End Sub

    Public Overloads Overrides Sub AddCategoryGroup(ByVal CategoryGroupDescription As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "INSERT INTO " & _TablePrefix & "_CategoryGroups(CategoryGroupId," & _
        "CategoryGroupDescription,CreatedBy,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) " & _
        "VALUES(?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@CategoryGroupId", System.Guid.NewGuid.ToString, DbType.String)
        DAP.AddParameter("@CategoryGroupDescription", CategoryGroupDescription, DbType.String)
        DAP.AddParameter("@CreatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@CreatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
    End Sub

    Public Overrides Sub DeleteCategory(ByVal CategoryId As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Categories " & _
        "SET LogicalDeleteInd = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE CategoryId = ?"
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@CategoryId", CategoryId, DbType.String)
    End Sub

    Public Overrides Sub DeleteCategoryGroup(ByVal CategoryGroupId As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_CategoryGroups " & _
        "SET LogicalDeleteInd = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE CategoryGroupId = ?"
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@CategoryGroupId", CategoryGroupId, DbType.String)
    End Sub

    Public Overloads Overrides Sub EditCategory(ByVal CategoryId As String, ByVal CategoryItem As DataTypes.CategoryItem, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Categories " & _
        "SET CategoryGroupId = ?, CategoryShortDescription = ?, CategoryLongDescription = ?, " & _
        "SortOrder = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE CategoryId = ?"
        DAP.AddParameter("@CategoryGroupId", CategoryItem.CategoryGroupId, DbType.String)
        DAP.AddParameter("@CategoryShortDescription", CategoryItem.CategoryShortDescription, DbType.String)
        DAP.AddParameter("@CategoryLongDescription", CategoryItem.CategoryLongDescription, DbType.String)
        DAP.AddParameter("@SortOrder", CategoryItem.SortOrder, DbType.Int64)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        DAP.AddParameter("@CategoryId", CategoryId, DbType.String)
    End Sub

    Public Overloads Overrides Sub EditCategory(ByVal CategoryId As String, ByVal CategoryGroupId As String, ByVal CategoryShortDescription As String, ByVal CategoryLongDescription As String, ByVal SortOrder As Integer, ByVal ParentCategoryId As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Categories " & _
        "SET CategoryGroupId = ?, CategoryShortDescription = ?, CategoryLongDescription = ?, " & _
        "SortOrder = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE CategoryId = ?"
        DAP.AddParameter("@CategoryGroupId", CategoryGroupId, DbType.String)
        DAP.AddParameter("@CategoryShortDescription", CategoryShortDescription, DbType.String)
        DAP.AddParameter("@CategoryLongDescription", CategoryLongDescription, DbType.String)
        DAP.AddParameter("@SortOrder", SortOrder, DbType.Int64)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        DAP.AddParameter("@CategoryId", CategoryId, DbType.String)
    End Sub

    Public Overloads Overrides Sub EditCategoryGroup(ByVal CategoryGroupId As String, ByVal CategoryGroupItem As DataTypes.CategoryGroupItem, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_CategoryGroups " & _
        "SET CategoryGroupDescription = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE CategoryGroupid = ?"
        DAP.AddParameter("@CategoryGroupDescription", CategoryGroupItem.CategoryGroupDescription, DbType.String)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        DAP.AddParameter("@CategoryGroupId", CategoryGroupId, DbType.String)
    End Sub

    Public Overloads Overrides Sub EditCategoryGroup(ByVal CategoryGroupId As String, ByVal CategoryGroupDescription As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_CategoryGroups " & _
        "SET CategoryGroupDescription = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE CategoryGroupid = ?"
        DAP.AddParameter("@CategoryGroupDescription", CategoryGroupDescription, DbType.String)
        DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        DAP.AddParameter("@CategoryGroupId", CategoryGroupId, DbType.String)
    End Sub

    Public Overloads Overrides Function GetCategories() As System.Collections.Generic.List(Of DataTypes.CategoryItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_Categories WHERE LogicalDeleteInd = ?"
        DAP.AddParameter("@LogicalDeleteInd", cFalseValue, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        Dim returnVal As New Generic.List(Of DataTypes.CategoryItem)
        For Each myReader As Common.DbDataRecord In result
            Dim AddItem As New DataTypes.CategoryItem
            AddItem.CategoryGroupId = myReader.Item("CategoryGroupId")
            AddItem.CategoryId = myReader.Item("CategoryId")
            AddItem.CategoryShortDescription = myReader.Item("CategoryShortDescription")
            AddItem.CategoryLongDescription = myReader.Item("CategoryLongDescription")
            AddItem.CreateDate = myReader.Item("CreateDate")
            'AddItem.CreatedBy = 
        Next
    End Function

    Public Overloads Overrides Function GetCategories(ByVal SortColumn As String, ByVal SortOrder As String) As System.Collections.Generic.List(Of DataTypes.CategoryItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_Categories"
    End Function

    Public Overrides Function GetCategoriesByDesc(ByVal CategoryGroupDescription As String) As System.Collections.Generic.List(Of DataTypes.CategoryItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_Categories"
    End Function

    Public Overrides Function GetCategoriesById(ByVal CategoryGroupId As String) As System.Collections.Generic.List(Of DataTypes.CategoryItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_Categories"
    End Function

    Public Overloads Overrides Function GetCategoryGroups() As System.Collections.Generic.List(Of DataTypes.CategoryGroupItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_CategoryGroups"
    End Function

    Public Overloads Overrides Function GetCategoryGroups(ByVal SortColumn As String, ByVal SortOrder As String) As System.Collections.Generic.List(Of DataTypes.CategoryGroupItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_CategoryGroups"
    End Function

    Public Overloads Overrides Function GetCategoryGroups(ByVal SearchColumn As String, ByVal SearchOperator As String, ByVal SearchCriteria As String) As System.Collections.Generic.List(Of DataTypes.CategoryGroupItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_CategoryGroups"
    End Function

    Public Overrides Function GetCateogries(ByVal SearchColumn As String, ByVal SearchOperator As String, ByVal SearchCriteria As String) As System.Collections.Generic.List(Of DataTypes.CategoryItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT * FROM " & _TablePrefix & "_Categories"
    End Function

    Private Function CBoolToYesNo(ByVal InputValue As Boolean) As String
        If InputValue = True Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function

    Private Function CYesNoToBool(ByVal InputString As String) As Boolean
        If InputString.ToLower = "y" Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Function GetConfigValue(ByVal configValue As String, ByVal defaultValue As String) As String
        If String.IsNullOrEmpty(configValue) Then
            Return defaultValue
        End If
        Return configValue
    End Function
End Class
